A well known database software program is DATABASE 2 (DB2) database software distributed by IBM Corporation. As is known in the art, DB2 operates as a subsystem in a computer system operating under the IBM MVS operating system software. In a DB2 environment, user data resides in DB2 tables which are in tablespaces. A tablespace is, for example, a portion of storage space in a direct access storage device (DASD) such as a disk drive. For exemplary purposes, illustrated below is an order.sub.-- entry table that would be stored in a tablespace. The order.sub.-- entry table contains columns: customer.sub.-- number; product.sub.-- code; order.sub.-- number; buyer.sub.-- name; and ship.sub.-- to.sub.-- zip.
__________________________________________________________________________ Order.sub.-- Entry Table customer.sub.-- number product.sub.-- code order.sub.-- number buyer.sub.-- name ship.sub.-- to.sub.-- zip __________________________________________________________________________ 1111111111 0010 1234500001 John Doe 60606 1111111111 0040 1234500002 Jane Doe 70707 3333333333 0020 1234500003 Bill Smith 90909 2222222222 0030 1234500004 Fred Smith 80808 __________________________________________________________________________
While the above Order.sub.-- Entry table shows four rows, the table could have millions of rows for all the orders of a company, for example 4 million rows. The order.sub.-- entry table also has, for example, three index keys and two foreign keys. An index key is an identifier for a particular row of a table while a foreign key also identifies a row but is also used for referential integrity as described below. For example, in the order.sub.-- entry table, one index key could be based on Order.sub.-- Number, another index key based on buyer.sub.-- name and a third index key based on ship.sub.-- to.sub.-- zip. As is known in the art, an index key for a particular table indicates a row identification (RID) and a selected value for the row (e.g., the index key value).
The index key can be used to generate an index for the table which facilitates subsequent searches for particular data in the table. For example, the Order.sub.-- Entry table would have three indexes (e.g., one for each index key), each index being stored in an indexspace. Similar to a tablespace, an indexspace is, for example, a designated portion of a DASD. Thus, if a user was looking for rows that contain a particular buyer name in the Order.sub.-- Entry table, the database management system could query the buyer index for the table to identify all occurrences of the buyer name without reading the entire table to locate the rows.
DB2 administrators analyze performance characteristics for application programs that access a database table in an attempt to find the optimum index structure for fast access to the database table. The values to be used as an index must be carefully selected because each index results in overhead for the database system. For example, each transaction in a database table, such as an add or delete, requires that each index for the table also be updated. Thus, it is desirable that the number of indexes for a table be minimized to enhance the performance of application programs. The values to be used as an index for a database table are selected based on, for example, data accessed most frequently by users of the table, generally on-line transaction users. Index keys generally are not based on foreign keys, as foreign keys are used primarily for validation purposes (e.g., constraint enforcement).
As is known in the art, each table in a database may be either a parent table, a child table or both. A child table is related to a parent table via the foreign key value or values contained in columns of the child table. For example, a foreign key value can appear multiple times in a child table (e.g., multiple rows in a child table can have the same foreign key, such as the customer.sub.-- number and product.sub.-- code entries in the order.sub.-- entry table) but each foreign key must be associated with a unique key in a parent table of the child table.
Referential integrity ensures that every foreign key value is valid (e.g., has a corresponding primary key in a parent table). Thus, referential integrity (RI) means that a value in the column of a row in the table is valid when this value also exists in an index of another table. A row should not be in a table if it violates a constraint. As the order.sub.-- entry table illustrated above has two foreign keys, it has a RI constraint on customer.sub.-- number and product.sub.-- code. As is known in the art, when a user of a DB2 database management system creates a table, the user also defines the constraints for the table (e.g., the user can define the relational integrity criteria). Illustrated below are an exemplary product table and an exemplary customer table (e.g., the parent tables for the foreign keys in the order.sub.-- entry table).
______________________________________ Product Table product.sub.-- code product.sub.-- description retail.sub.-- price ______________________________________ 00010 laptop pc 1000.00 desktop pc 1100.00 00020 00030 office pc 1200.00 00040 lan pc 3500.00 00050 home pc 999.99 ______________________________________
The product table show five rows, although the table could have thousands of rows for all of the different products of a company. The product table has, for example, an index based on the column product.sub.-- code, which values are illustrated in ascending order. The values in the column product.sub.-- code are each unique since there is only one product code assigned to each product and thus in this table, a product code would not be included more than once. Accordingly, an index for the product table would include the key value (e.g., the stored value in the product.sub.-- code column) and a RID. The product table index would reside in a DB2 indexspace.
The customer table illustrated below shows four rows, although this table could also have thousands of rows for all of the customers of a company. The customer table has, for example, an index based on the column customer.sub.-- number, which values are illustrated in ascending order. The values in the column customer.sub.-- number are each unique since there is only one customer number assigned to each customer name and thus a customer number would not be included in this table more than once. Accordingly, an index for the customer table would include the key value (e.g., the value of the column customer.sub.-- number) and a RID. The customer index would also reside in a DB2 indexspace.
______________________________________ Customer Table customer.sub.-- number buyer.sub.-- name customer.sub.-- address ______________________________________ 1111111111 John Doe State A 2222222222 Fred Smith State B 3333333333 Bill Smith State C 4444444444 Steve Jones State D ______________________________________
As shown by the above tables, all of the rows in the Order.sub.-- Entry table are valid (e.g., there are no referential integrity constraint violations) because the foreign key values in the column product.sub.-- code of the Order.sub.-- Entry table also exist in the product table and the values in the column customer.sub.-- number of the Order.sub.-- Entry table also exist in the customer table.
Conventional database management systems, such as DB2, provide the user with the ability to identify specific conditions that a row must meet before it can be added to a table. These conditions are referred to as "constraints" because they constrain the values that a row may include. Constraints include, for example, check constraints and referential integrity constraints. Check constraints include, for example, qualifying criteria for a particular value, such as a zip code value (e.g., the ship.sub.-- to.sub.-- zip value in the Order.sub.-- Entry table) being in the range of 00000 to 99999. As discussed above, referential integrity constraints ensure that a value in a row of a table is valid when the value also exists in an index of another table.
Constraint enforcement can be performed prior to loading of data into a database table or after data has already been loaded into a database table. When constraint enforcement is performed after loading data into a database table, for example as part of a recovery operation following a hardware of software failure, the constraint enforcement is generally performed by a CHECK utility, such as CHECK DATA by IBM Corp., CHECK PLUS by BMC Software and FASTCHECK by Platinum technology, inc.
Conventional CHECK utilities ensure that data in the table do not violate any constraints that have been established for the table. Constraints can be established at the time the table is generated. For example, constraints can be defined when the table is originally created in the database system and are stored in the DB2 catalog, which can be subsequently queried by a CHECK utility to identify the constraint information. A description of how to read the DB2 catalog is contained in co-pending application Ser. No. 09/151,750 entitled DYNAMIC DETERMINATION OF OPTIMAL PROCESS FOR ENFORCING CONSTRAINTS, assigned to the assignee of the present application and which is hereby expressly incorporated by reference.
To perform constraint enforcement, a conventional CHECK utility would, for example, be initialized and identify any applicable constraints for the table to be checked by reading the DB2 catalog, as is known in the art. The CHECK utility would, for example, then read each row of the database table and check for check constraint violations and/or referential integrity constraint violations.
Some DB2 tables have referential integrity constraints. A typical user application system has, for example, three parent tables and thirty one dependent tables. Some customers have as many as twenty one parent tables for one application system. There are various circumstances in which a database table is altered. For example, the table may be recovered from a backup file or reloaded with a new set of data. When a parent table is reloaded or recovered, the dependent tables may be out of synchronization with their respective parent table. For example, a recovered table may have used a backing file that was several days old and thus the recovered table may be out of synch with a dependent table which was in synch with the pre-recovered parent table.
Accordingly, the recover utility or the reload utility must identify all of the dependent tables for each parent tables that has been recovered or reloaded and set these dependent tables to a "check pending" status. Identification of the dependent table can be accomplished, for example, by reading the DB2 catalog as described above. As is known in the art, dependent tables in check pending this status are not accessible to the user programs until a CHECK utility is executed and any referential integrity violations corrected.
When recovering or reloading a tablespace with defined referential integrity constraints, the customer must run the Report Tablespace-Set utility which will generate a listing of all dependent tablespaces that needs to be checked.
For a reload operation in conventional database systems, the user needs to code and run a conventional Load utility naming the tables that should be reloaded. The following example is such a control statement to be provided to a conventional Load utility.
__________________________________________________________________________ LOAD RESUME NO REPLACE INTO TABLE CTINLAND.TBHAMEP1 LOAD RESUME NO REPLACE INTO TABLE CTINLAND.TBHAMEP2 LOAD RESUME NO REPLACE INTO TABLE CTINLAND.TBHAMEP3 __________________________________________________________________________
As the reload operation puts the dependent tables of the reloaded table into a check pending status, the user of the Load utility also needs to code and run a control statement for a conventional CHECK utility naming all the dependent tablespaces. An example of such a control statement to be provided to a conventional CHECK utility is as follows.
______________________________________ CHECK DATA , TABLESPACE JTINLAND.SCHORDCT , TABLESPACE JTINLAND.SCHITMDI , TABLESPACE JTINLAND.SCHHDINF , TABLESPACE JTINLAND.SCHREFL2 , TABLESPACE JTINLAND.SCHCMAIL , TABLESPACE JTINLAND.SCHBILDG , TABLESPACE JTINLAND.SCHFACFW , TABLESPACE JTINLAND.SCHEHRAC , TABLESPACE JTINLAND.SCHCOATI , TABLESPACE JTINLAND.SCHCTGCM , TABLESPACE JTINLAND.SCHSSTCK , TABLESPACE JTINLAND.SCHSRORD , TABLESPACE JTINLAND.SCPEPRCE , TABLESPACE JTINLAND.SQOMFGHD , TABLESPACE JTINLAND.SQOMFCHI , TABLESPACE JTINLAND.SQOMFCMT , TABLESPACE JTINLAND.SQOMFOVR , TABLESPACE JTINLAND.SQOMFOVU , TABLESPACE JTINLAND.SQOMFMPL , TABLESPACE JTINLAND.SQOMFOVP , TABLESPACE JTINLAND.SQOMFCHE , TABLESPACE JTINLAND.SQOMFUNT , TABLESPACE JTINLAND.SQOMFTRQ , TABLESPACE JTINLAND.SCHEITMR , TABLESPACE JTINLAND.SCHORDFW , TABLESPACE JTINLAND.SCHORHLD , TABLESPACE JTINLAND.SCHREFRL , TABLESPACE JTINLAND.SCHRETYP , TABLESPACE JTINLAND.SCPEPICS , TABLESPACE JTINLAND.SCHERCYL , TABLESPACE JTINLAND.SCHCACKW ______________________________________
For a recovery operation in conventional database systems, the user needs to code and run a conventional recover utility naming the tablespace that should be recovered.
The following example is a control statement for a conventional recover utility.
______________________________________ RECOVER TABLESPACE JTINLAND.SCHAMEP1 TABLESPACE JTINLAND.SCHAMEP2 TABLESPACE JTINLAND.SCHAMEP3 ______________________________________
Further, the user needs to code and run a control statement for a conventional Check utility naming all the dependent tablespaces from, for example, the Report Tablespace-Set utility. An example of a control statement to be provided to a conventional Check utility is as follows.
______________________________________ CHECK DATA , TABLESPACE JTINLAND.SCHORDCT , TABLESPACE JTINLAND.SCHITMDI , TABLESPACE JTINLAND.SCHHDINF , TABLESPACE JTINLAND.SCHREFL2 , TABLESPACE JTINLAND.SCHCMAIL , TABLESPACE JTINLAND.SCHBILDG , TABLESPACE JTINLAND.SCHFACFW , TABLESPACE JTINLAND.SCHEHRAC , TABLESPACE JTINLAND.SCHCOATI , TABLESPACE JTINLAND.SCHCTGCM , TABLESPACE JTINLAND.SCHSSTCK , TABLESPACE JTINLAND.SCHSRORD , TABLESPACE JTINLAND.SCPEPRCE , TABLESPACE JTINLAND.SQOMFGHD , TABLESPACE JTINLAND.SQOMFCHI , TABLESPACE JTINLAND.SQOMFCMT , TABLESPACE JTINLAND.SQOMFOVR , TABLESPACE JTINLAND.SQOMFOVU , TABLESPACE JTINLAND.SQOMFMPL , TABLESPACE JTINLAND.SQOMFOVP , TABLESPACE JTINLAND.SQOMFCHE , TABLESPACE JTINLAND.SQOMFUNT , TABLESPACE JTINLAND.SQOMFTRQ , TABLESPACE JTINLAND.SCHEITMR , TABLESPACE JTINLAND.SCHORDFW , TABLESPACE JTINLAND.SCHORHLD , TABLESPACE JTINLAND.SCHREFRL , TABLESPACE JTINLAND.SCHRETYP , TABLESPACE JTINLAND.SCPEPICS , TABLESPACE JTINLAND.SCHERCYL , TABLESPACE JTINLAND.SCHCACKW ______________________________________
Therefore, recovery of a parent tablespace or reload of a parent table involves substantial efforts by the user of utility to identify all of the dependent tablespaces to be checked.
Furthermore, in a mainframe environment running MVS or a similar operating system, when a program executes, that program is considered to be a job for MVS. For example, when a customer executes a utility, the utility is a job step that must be executed. When the recover operation is executed, it is a subtask of the utility job step. Similarly, when a reload operation is executed, it is also a subtask of the utility job step. When a check operation is executed, it is also a subtask of the utility job step. A MVS job may have many tasks. Execution of a subtask requires virtual storage (e.g., memory).
MVS will provide the virtual storage whenever a subtask needs it. For example, when a recover utility or a reload utility identifies the dependent tables associated with the parent table being recovered or reloaded, the utility reads the DB2 catalog and reads the dependent table names into virtual storage allocated for the subtask so that, for example, the dependent tables can be placed in a check pending status following a recover or reload action. When the subtask terminates, MVS will clean up and any allocated data area (e.g., virtual storage) will be freed by MVS so that another subtask can use the data area. Accordingly, the data contained in the freed storage are (e.g., the names of the dependent tablespaces) is deleted when the data area is freed and thus the deleted information is not available for subsequent use, for example by the check utility that needs to check each dependent table.